﻿using System.Collections.Generic;
using System.Linq;
using System.Text;
using Foothill.Common;
using Foothill.Model;

namespace Foothill.DataAccess.Repository
{
    internal static class AdSearchRepoSqlGenerator
    {
        public static string _selectSql = @" SELECT 
			     AdForSale.AdId
			    ,AdForSale.ModelId AS ModelId
			    ,vVehicleMakeModel.MakeId AS MakeId
			    ,vVehicleMakeModel.ModelName
			    ,vVehicleMakeModel.MakeName
			    ,AdForSale.TransmissionTypeId
			    ,TransmissionType.TransmissionTypeName
			    ,YearMade
			    ,Price
			    ,MileAge
			    ,TimePosted
			    ,AdForSale.ContactPhone
			    ,AdForSale.ContactEmail
                ,AdForSale.ContactName
			    ,AdForSale.StatusId
			    ,AdForSale.CityId
			    ,City.CityName
			    ,LastEditTime
			    ,[Description]
			    ,EditKey
                ,NumClickedOn
                ,NumSaved
                ,[PostedByUserAccountId]
			    ,tProfilePhoto.ProfilePhotoPath
                ,tPhotoCount.ThePhotoCount
			    ,VehicleCondition.VehicleConditionName
                ,ListingType.ListingTypeName
                ,ListingType.ListingTypeId
                ,UserAccount.UserLogoFilePath 

                FROM AdForSale 

	            LEFT OUTER JOIN vVehicleMakeModel ON
		        vVehicleMakeModel.ModelId = AdForSale.ModelId

                LEFT OUTER JOIN VehicleCondition ON
		        VehicleCondition.VehicleConditionId = AdForSale.VehicleConditionId

                LEFT OUTER JOIN City ON
		        City.CityId = AdForSale.CityId

                LEFT OUTER JOIN TransmissionType ON
		        TransmissionType.TransmissionTypeId = AdForSale.TransmissionTypeId

                LEFT OUTER JOIN ListingType ON 
                ListingType.ListingTypeId = AdForSale.ListingTypeId

                LEFT OUTER JOIN UserAccount ON
                UserAccount.UserAccountId = AdForSale.PostedByUserAccountId

                LEFT OUTER JOIN
                (SELECT Photo.AdId, Photo.PathOnServer AS ProfilePhotoPath FROM Photo WHERE Photo.DisplayPosition = 0) tProfilePhoto
                ON AdForSale.AdId = tProfilePhoto.AdId

                LEFT OUTER JOIN
                (SELECT Photo.AdId,COUNT(*) AS ThePhotoCount FROM Photo GROUP BY Photo.AdId) tPhotoCount
                ON tPhotoCount.AdId = AdForSale.AdId
            
            ";
        
        public static string ConstructAdvancedSearchQuery(ForSaleAdAdvancedSearchParameter searchParam)
        {
            StringBuilder sb = new StringBuilder(_selectSql);

            string whereClause = ConstructWhereClause(searchParam);

            sb.Append(whereClause);

            // add order by clause
            sb.Append(ConstructOrderByClause(searchParam.OrderByItem, searchParam.SortDirection));

            string testSql = ConstructAdvancedSearchSqlForDebug(sb.ToString(), searchParam);
            int i = testSql.Length;

            return sb.ToString();
        }

        public static string GetPostsByUserSql(string userIdParamName)
        {
            return _selectSql + " WHERE PostedByUserAccountId = @" + userIdParamName;
        }

        public static string ConstructGetUserSavedPostsSql(string userIdParamName)
        {
            string sql = _selectSql + " INNER JOIN UserSavedPost ON UserSavedPost.PostId = AdForSale.AdId ";
            return sql + " WHERE UserSavedPost.UserAccountId = @" + userIdParamName;
        }

        public static string ConstructGetByAdIdListSql(IList<int> adIdList)
        {
            if (!adIdList.Any())
            {
                return string.Empty;
            }

            string[] paramNames = adIdList.Select((s, i) => "@postId" + i.ToString()).ToArray();
            string inClause = string.Join(",", paramNames);

            return _selectSql + string.Format(" WHERE AdForSale.AdId IN ({0}) ", inClause);
        }

        private static string ConstructOrderByClause(OrderByItems orderByItem, fhSortDirection direction)
        {
            string result = " ORDER BY ";
            switch (orderByItem)
            {
                case OrderByItems.ByKilometers:
                    result += "MileAge";
                    break;
                case OrderByItems.ByLastEditTime:
                    result += "LastEditTime";
                    break;
                case OrderByItems.ByModel:
                    result += "ModelId";
                    break;
                case OrderByItems.ByPrice:
                    result += "Price";
                    break;
                case OrderByItems.ByYearAge:
                    result += "YearMade";
                    break;
                default:
                    result += "LastEditTime";
                    break;
            }

            if (direction == fhSortDirection.DESC)
            {
                // do not remove space
                result += " DESC ";
            }

            return result;
        }

        // construct where clause for AdvancedSearch
        private static string ConstructWhereClause(ForSaleAdAdvancedSearchParameter searchParam)
        {
            StringBuilder sb = new StringBuilder();

            // active post only
            sb.Append(" WHERE StatusId = " + (int)AdStatusLookup.Active);

            // make and model
            if (!searchParam.IncludeAllMakes)
            {
                sb.Append(GetMakeAndModelWhereClause(searchParam));
            }

            // year
            if (searchParam.ContainRangeBasedCriteria(RangeBasedCriteria.YearMade))
            {
                sb.Append(" AND (AdForSale.YearMade >= @YearFrom AND AdForSale.YearMade <= @YearTo)  ");
            }

            int min = 0;
            int max = 0;

            // kilometers
            if (searchParam.ContainRangeBasedCriteria(RangeBasedCriteria.Kilometer, out min, out max))
            {
                if (max != GlobalValues.DropdownMaxValue)
                {
                    // user didn't select "> 150k" 
                    sb.Append(" AND ( AdForSale.MileAge >= @KiloFrom AND AdForSale.MileAge <= @KiloTo ) ");
                }
                else
                {
                    // user selected "> 150k" 
                    sb.Append(" AND ( AdForSale.MileAge >= @KiloFrom ) ");
                }
            }

            // price
            if (searchParam.ContainRangeBasedCriteria(RangeBasedCriteria.Price, out min, out max))
            {
                if (max != GlobalValues.DropdownMaxValue)
                {
                    sb.Append(" AND ( AdForSale.Price >= @PriceFrom AND AdForSale.Price <= @PriceTo ) ");
                }
                else
                {
                    sb.Append(" AND ( AdForSale.Price >= @PriceFrom ) ");
                }
            }

            // with photo only
            if (searchParam.WithPhotosOnly)
            {
                sb.Append(" AND ( ThePhotoCount > 0 ) ");
            }

            // condition
            if (searchParam.ConditionId != (int)VehicleCondition.Both)
            {
                sb.Append(" AND ( AdForSale.VehicleConditionId = @ConditionId ) ");
            }

            // listing type
            if (searchParam.ListingTypeId != (int)ListingTypes.Both)
            {
                sb.Append(" AND ( AdForSale.ListingTypeId = @ListingTypeId ) ");
            }

            // transmission type
            if (searchParam.TransmissionTypeId != (int)TransmissionType.Both)
            {
                sb.Append(" AND ( AdForSale.TransmissionTypeId = @TransmissionTypeId ) ");
            }

            // location list
            if (!searchParam.AllLocationSelected)
            {
                sb.Append(GetLocationsWhereClause(searchParam.LocationIdList));
            }

            return sb.ToString();
        }

        // todo: write unit tests for this method
        internal static string GetMakeAndModelWhereClause(ForSaleAdAdvancedSearchParameter searchParam)
        {
            if (searchParam.IncludeAllMakes)
            {
                return string.Empty;
            }

            IList<int> modelIdList;
            IList<int> makeIdList;

            searchParam.GetSelectedMakeAndModelIdList(out makeIdList, out modelIdList);

            string makesWhereClause = GetMakesWhereClause(makeIdList);
            string modelWhereClause = GetModelsWhereClause(modelIdList);

            if (string.IsNullOrEmpty(makesWhereClause))
            {
                if (!string.IsNullOrEmpty(modelWhereClause))
                {
                    // no make, has model
                    return string.Format(" AND {0} ", modelWhereClause);
                }
                else
                {   // no make or model
                    return string.Empty;
                }
            }
            else
            {
                if (!string.IsNullOrEmpty(modelWhereClause))
                {
                    // has make and model
                    return string.Format(" AND ({0} OR {1})", makesWhereClause, modelWhereClause);
                }
                else
                {
                    // has make , no model
                    return string.Format(" AND {0} ", makesWhereClause);
                }
            }
        }

        private static string GetLocationsWhereClause(List<int> locations)
        {
            // when no location selected, treated the same as all locations selected
            if (locations.Count == 0)
            {
                return "";
            }

            string[] paramNames = locations.Select((s, i) => "@location" + i.ToString()).ToArray();
            string inClause = string.Join(",", paramNames);

            string result = string.Format(" AND AdForSale.CityId IN ({0}) ", inClause);

            return result;
        }

        private static string GetMakesWhereClause(IList<int> makeIdList)
        {
            if (makeIdList.Count == 0)
            {
                return string.Empty;
            }

            string[] paramNames = makeIdList.Select((s, i) => "@make" + i.ToString()).ToArray();
            string inClause = string.Join(",", paramNames);

            string result = string.Format(" MakeId IN ({0}) ", inClause);

            return result;
        }

        private static string GetModelsWhereClause(IList<int> modelIdList)
        {
            if (modelIdList.Count == 0)
            {
                return string.Empty;
            }

            string[] paramNames = modelIdList.Select((s, i) => "@model" + i.ToString()).ToArray();
            string inClause = string.Join(",", paramNames);

            string result = string.Format(" AdForSale.ModelId IN ({0}) ", inClause);

            return result;
        }

        // used for debug, replace params with acutal value, return a sql string that can be run in SSMS
        private static string ConstructAdvancedSearchSqlForDebug(string sqlWithParams, ForSaleAdAdvancedSearchParameter searchParam)
        {
            RangeBasedAdSearchCriteria criteria;

            // year
            if (searchParam.TryGetRangeBasedCriteria(RangeBasedCriteria.YearMade, out criteria))
            {
                sqlWithParams = sqlWithParams.Replace("@YearFrom", criteria.Min.ToString());
                sqlWithParams = sqlWithParams.Replace("@YearTo", criteria.Max.ToString());
            }

            // kilometer
            if (searchParam.TryGetRangeBasedCriteria(RangeBasedCriteria.Kilometer, out criteria))
            {
                sqlWithParams = sqlWithParams.Replace("@KiloFrom", criteria.Min.ToString());
                sqlWithParams = sqlWithParams.Replace("@KiloTo", criteria.Max.ToString());
            }

            // price
            if (searchParam.TryGetRangeBasedCriteria(RangeBasedCriteria.Price, out criteria))
            {
                sqlWithParams = sqlWithParams.Replace("@PriceFrom", criteria.Min.ToString());
                sqlWithParams = sqlWithParams.Replace("@PriceTo", criteria.Max.ToString());
            }

            sqlWithParams = sqlWithParams.Replace("@ConditionId", searchParam.ConditionId.ToString());
            sqlWithParams = sqlWithParams.Replace("@ListingTypeId", searchParam.ListingTypeId.ToString());
            sqlWithParams = sqlWithParams.Replace("@TransmissionTypeId", searchParam.TransmissionTypeId.ToString());

            // location list
            if (!searchParam.AllLocationSelected)
            {
                for (int i = 0; i < searchParam.LocationIdList.Count; ++i)
                {
                    string localtionParam = string.Format("@location{0}", i);
                    sqlWithParams = sqlWithParams.Replace(localtionParam, string.Format("'{0}'", searchParam.LocationIdList[i]));
                }
            }

            if (!searchParam.IncludeAllMakes)
            {
                IList<int> makeIdList;
                IList<int> modelIdList;

                searchParam.GetSelectedMakeAndModelIdList(out makeIdList, out modelIdList);

                for (int i = 0; i < makeIdList.Count; ++i)
                {
                    string makeParam = string.Format("@make{0}", i);
                    sqlWithParams = sqlWithParams.Replace(makeParam, string.Format("'{0}'", makeIdList[i]));
                }

                for (int i = 0; i < modelIdList.Count; ++i)
                {
                    string modelParam = string.Format("@model{0}", i);
                    sqlWithParams = sqlWithParams.Replace(modelParam, string.Format("'{0}'", modelIdList[i]));
                }
            }

            sqlWithParams = sqlWithParams.Replace("@PageNum", searchParam.PageNum.ToString());
            sqlWithParams = sqlWithParams.Replace("@PageSize", searchParam.PageSize.ToString());

            return sqlWithParams;
        }
    }
}
